package com.gopay.controller;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.RandomAccessFile;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.mail.MessagingException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import com.gopay.checkorder.service.BcdlOrderSendBankService;
import com.gopay.common.constants.bank.BankConstants;
import com.gopay.common.constants.bank.BankOrderType;
import com.gopay.common.constants.bank.BisBankBusinessProd;
import com.gopay.common.constants.proccode.ProcCodeConstants;
import com.gopay.common.domain.bank.BcdlMessage;
import com.gopay.common.domain.bank.BcdlResult;
import com.gopay.common.domain.bank.icbc.bcdl.pay.IcbcMessageData;
import com.gopay.common.order.dao.CommonQueryDAO;
import com.gopay.common.util.DateUtils;
import com.gopay.common.util.SendMail;
import com.gopay.common.util.StringTools;
import com.gopay.common.util.spring.SpringContextUtil;

/**
 * 手动发起工行网银明细文件下载
 * 
 * @IcbcQryAcctDetailController.java
 * @author lilili
 * @2017-7-24 下午1:27:13 www.gopay.com.cn Inc.All rights reserved.
 */
@Controller
@Scope("prototype")
public class IcbcQryAcctDetailController {
	 private final static Logger logger = LoggerFactory.getLogger("CHECK_ORDER");
	    @Resource(name = "acctDetailConfig")
	    private Map<String, String> acctDetailConfig;
	    @Resource(name = "commonQueryDAO")
	    private CommonQueryDAO commonQueryDAO;

	    private int MAX_QUERY_SIZE = 1000; // 最大查询次数
	    private int COUNT_SIZE = 100; // 包内最大订单数
	    private String filePath = "/usr/local/ciecc/webapps/upload/ICBC/transDetail/";
	    
	    /**
		 * 
		 * @param HttpServletRequest
		 * @param HttpServletResponse
		 * @return ModelAndView
		 */
		@RequestMapping(value = "/icbcQryAcctDetail", method = { RequestMethod.GET })
		protected void execute(HttpServletRequest request,
				HttpServletResponse response) {
			  logger.error("IcbcQryAcctDetailController: start ");
		        try {
		            if ("off".equals(acctDetailConfig.get("mailSwitch"))) {
		                logger.error("IcbcQryAcctDetailController: 不进行查询操作直接返回!");
		                return;
		            }
		            
		            //格式为yyyyMMddHHmmss
		            final String fTime = request.getParameter("date");
		            Thread t = new Thread(new Runnable(){
		                public void run(){
		                    getDataDetail(fTime);
		                }});  
		            t.start();  
		            return;
		        } catch (Exception e) {
		            logger.error("IcbcQryAcctDetailController error: ", e);
		        }
		}
		
		 public void getDataDetail(String fTime){
		     logger.error("IcbcQryAcctDetailController getDataDetail: start ");
             try {
                 
                 int querySize = getBcdlOrderCount(fTime.substring(0, 8)); // 订单查询数
                 List<String> fileNames = new ArrayList<String>(); // 网银明细文件
                 getAcctDetail(fTime, querySize + MAX_QUERY_SIZE);
                 fileNames.add("ICBC_BCDL_DETAILS_" + fTime + ".xlsx");
                 // 发送邮件
                 sendMail(fileNames);
             } catch (Exception e) {
                 logger.error("IcbcQryAcctDetailController error: ", e);
             }
		 }
		
		 public void getAcctDetail(String fileTime, int querySize) throws IOException {
		        String beanName = BankConstants.BANK_LOGO_ICBC + "-BCDL";
		        BcdlOrderSendBankService sendBankService = (BcdlOrderSendBankService) SpringContextUtil.getBean(beanName);
		        String nextTag = ""; // 网银明细查询下页标识
		        String icbcFilePath = filePath + "txt/ICBC_BCDL_DETAILS_" + fileTime + ".txt";
		        File file = new File(icbcFilePath);
		        
		        // 判断查询文件是否已存在
                if (file.exists() && file.length() > 0) {
                    String lastLine = readLastLine(file, "UTF-8");
                    if (lastLine.trim().equals("########")) {
                        // 网银明细已下载
                        logger.error("IcbcQryAcctDetailController " + file.getName() + " is download");
                        parseFile(file, fileTime);
                        return;
                    }
                }
		        
		        // 循环发起查询请求
		        for (int i = 0; i < querySize; i++) {
		            logger.error("IcbcQryAcctDetailController sendQuery start size: " + i + " nextTag: " + nextTag);
		            BcdlMessage req = new BcdlMessage();
		            req.setBank(BankConstants.BANK_LOGO_ICBC);
		            req.setOrderDate(fileTime);
		            req.setSpecial(nextTag); // 查询下页标识
		            
		            BcdlResult result = sendBankService.queryAcctDetail(req);
		            if (!ProcCodeConstants.PROC_CODE_100S1000.equals(result.getResultCode())) {
		                logger.error("IcbcQryAcctDetailController getAcctDetail error errorCode: " + result.getResultCode()
		                        + "errorMsg: " + result.getErrorMsg());

		                // 查询失败获取上次结果下标继续查询
		                String lastLine = readLastLine(file, "UTF-8");
		                if (file.exists() && file.length() > 0) {
		                    if (lastLine.trim().equals("########")) {
		                        // 网银明细已下载
		                        logger.error("IcbcQryAcctDetailTaskImpl " + file.getName() + " is download");
		                        parseFile(file, fileTime);
		                        break;
		                    } else {
		                        // 查询下页标识
		                        nextTag = getNextTag(icbcFilePath);
		                    }
		                }
		                continue;
		            }
		            
		            // 查询成功
		            IcbcMessageData detailData = (IcbcMessageData) result.getModel("acctDetail");
		            if (StringUtils.isNotEmpty(detailData.getNextTag())) {
		                // 银行返回有下页标识继续查询
		                nextTag = detailData.getNextTag();
		            } else {
		                // 银行返回无下页标识跳出循环
		                logger.error("IcbcQryAcctDetailTaskImpl sendQuery end");
		                parseFile(file, fileTime);
		                break;
		            }
		        }
		    }
		 
		 public void parseFile(File file, String fileTime) {
		        logger.error("IcbcQryAcctDetailController parseFile start: ");
		        try {
		            String xmlPlain = "";
		            String plainMessage = "";
		            int insertSize = 0;
		            StringBuffer sb = new StringBuffer();
		            BufferedInputStream fis = new BufferedInputStream(new FileInputStream(file));
		            BufferedReader reader = new BufferedReader(new InputStreamReader(fis, "UTF-8"), 5 * 1024 * 1024);
		            while ((xmlPlain = reader.readLine()) != null) {
		                sb.append(xmlPlain);
		                if ("</CMS>".equals(xmlPlain)) {
		                    // 一次请求报文数据
		                    plainMessage = sb.toString();
		                    sb.setLength(0); // 清空StringBuffer
		                    ++insertSize;
		                    creatExcel(fileTime, getDetailData(plainMessage), insertSize);
		                }
		            }
		            reader.close();
		            fis.close();
		            logger.error("IcbcQryAcctDetailController parseFile end");
		        } catch (Exception e) {
		            logger.error("IcbcQryAcctDetailController parseFile error: ", e);
		        }
		    }
		 
		 public List<IcbcMessageData> getDetailData(String detailXml) throws DocumentException {
		        List<IcbcMessageData> details = new ArrayList<IcbcMessageData>();
		        Document document = DocumentHelper.parseText(detailXml);
		        Element out = (Element) document.selectSingleNode("//CMS/eb/out");

		        List resRd = document.selectNodes("/CMS/eb/out/rd");
		        Iterator rds = resRd.iterator();
		        while (rds.hasNext()) {
		            Element rd = (Element) rds.next();
		            IcbcMessageData detail = new IcbcMessageData();
		            detail.setCrvouhNo(rd.elementText("VouhNo"));
		            detail.setPayAccNo(out.elementText("AccNo"));
		            detail.setRecAccNo(rd.elementText("RecipAccNo"));
		            detail.setRecAccNameCN(rd.elementText("RecipName"));
		            // 格式化日期
		            String time = rd.elementText("Time");
		            String sdf = new String("yyyy-MM-dd-HH.mm.ss");
		            String sdf1 = new String("yyyy-MM-dd HH:mm:ss");
		            Date date = DateUtils.strToDate(time, sdf);
		            String transTime = DateUtils.format(date, sdf1);
		            detail.setTransTime(transTime);
		            detail.setDcblType(Integer.parseInt(rd.elementText("Drcrf")));
		            BigDecimal amount = new BigDecimal("0");
		            if (1 == detail.getDcblType()) {
		                amount = new BigDecimal(rd.elementText("DebitAmount"));
		            } else if (2 == detail.getDcblType()) {
		                amount = new BigDecimal(rd.elementText("CreditAmount"));
		            }
		            detail.setPayAmt(amount.movePointLeft(2).toString());
		            detail.setRecBankNo(rd.elementText("RecipBkNo1"));
		            detail.setSummary(rd.elementText("Summary"));
		            detail.setUseCN(rd.elementText("UseCN"));
		            detail.setPostScript(rd.elementText("PostScript"));
		            detail.setBalance(rd.elementText("Balance"));
		            detail.setReceiptInfo(rd.elementText("ReceiptInfo"));
		            details.add(detail);
		        }
		        return details;
		    }

		    public void creatExcel(String fileTime, List<IcbcMessageData> detailData, int insertSize) throws IOException {
		        logger.error("IcbcQryAcctDetailController creatExcel start: " + insertSize);
		        if (detailData == null || detailData.size() <= 0) {
                    return;
                }

                File file = new File(filePath + "xls/ICBC_BCDL_DETAILS_" + fileTime + ".xlsx");
                if (!file.exists()) {
                    XSSFWorkbook wb = new XSSFWorkbook();
                    XSSFSheet sheet = wb.createSheet("ICBC_BCDL_DETAILS_" + fileTime);

                    XSSFCellStyle style = wb.createCellStyle();
                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                    // 创建HSSFFont实例，设置字体、字号、加粗、颜色
                    XSSFFont font = wb.createFont();
                    font.setFontName(HSSFFont.FONT_ARIAL);// 字体
                    font.setFontHeightInPoints((short) 10);// 字号
                    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
                    font.setColor(HSSFColor.BLACK.index);// 颜色
                    style.setFont(font);

                    String[] titles = new String[] { "凭证号", "本方账号", "对方账号", "交易时间", "借/贷", "借方发生额", "贷方发生额", "对方行号", "摘要",
                            "用途", "对方单位名称", "余额", "个性化信息" };
                    XSSFRow firstRow = sheet.createRow(0);
                    XSSFRow titleRow = sheet.createRow(1);
                    
                    XSSFCell firstCell = firstRow.createCell(0);
                    firstCell.setCellValue("[HISTORYDETAIL]");
                    
                    for (int i = 0; i < titles.length; i++) {
                        XSSFCell cell = titleRow.createCell(i);
                        cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                        cell.setCellValue(new XSSFRichTextString(titles[i]));
                        cell.setCellStyle(style);
                    }
                    sheet.setDefaultColumnWidth((short) 25);

                    ByteArrayOutputStream os = new ByteArrayOutputStream();
                    wb.write(os);
                    byte[] xlsx = os.toByteArray();
                    OutputStream out = new FileOutputStream(file);
                    out.write(xlsx);
                    out.flush();
                    out.close();
                    os.close();
                    logger.error("IcbcQryAcctDetailController creatExcelTitles end: " + insertSize);
                }

                FileInputStream fs = new FileInputStream(file);
                XSSFWorkbook wb = new XSSFWorkbook(fs);
                XSSFSheet sheet1 = wb.getSheetAt(0);
                // 总行数
                int lastRowNo = sheet1.getLastRowNum() + 1;
                
		        for (int i = 0; i < detailData.size(); i++) {
		            IcbcMessageData data = detailData.get(i);
		            XSSFRow tmprow = sheet1.createRow(i + lastRowNo);
		            XSSFCell tmpcell0 = tmprow.createCell(0);
		            tmpcell0.setCellValue(data.getCrvouhNo());
		            
		            XSSFCell tmpcell1 = tmprow.createCell(1);
		            tmpcell1.setCellValue(data.getPayAccNo());

		            XSSFCell tmpcell2 = tmprow.createCell(2);
		            tmpcell2.setCellValue(data.getRecAccNo());

		            XSSFCell tmpcell3 = tmprow.createCell(3);
		            tmpcell3.setCellValue(data.getTransTime());

		            XSSFCell tmpcell4 = tmprow.createCell(4);
		            if (1 == data.getDcblType()) {
		                tmpcell4.setCellValue("借");
		            } else if (2 == data.getDcblType()) {
		                tmpcell4.setCellValue("贷");
		            }

		            XSSFCell tmpcell5 = tmprow.createCell(5);
		            if (1 == data.getDcblType()) {
		                tmpcell5.setCellValue(data.getPayAmt());
		            }

		            XSSFCell tmpcell6 = tmprow.createCell(6);
		            if (2 == data.getDcblType()) {
		                tmpcell6.setCellValue(data.getPayAmt());
		            }

		            XSSFCell tmpcell7 = tmprow.createCell(7);
		            tmpcell7.setCellValue(data.getRecBankNo());

		            XSSFCell tmpcell8 = tmprow.createCell(8);
		            tmpcell8.setCellValue(data.getSummary());
		            
		            XSSFCell tmpcell9 = tmprow.createCell(9);
		            tmpcell9.setCellValue(data.getUseCN());
		            
		            XSSFCell tmpcell10 = tmprow.createCell(10);
		            tmpcell10.setCellValue(data.getRecAccNameCN());
		            
		            XSSFCell tmpcell11 = tmprow.createCell(11);
		            tmpcell11.setCellValue(data.getBalance());
		            
		            XSSFCell tmpcell12 = tmprow.createCell(12);
		            tmpcell12.setCellValue(data.getReceiptInfo());
		        }

		        ByteArrayOutputStream os = new ByteArrayOutputStream();
		        wb.write(os);
		        byte[] xlsx = os.toByteArray();
		        OutputStream out = new FileOutputStream(file);
		        out.write(xlsx);
		        out.flush();
		        out.close();
		        os.close();
		        logger.error("IcbcQryAcctDetailController creatExcel end: " + insertSize);
		    }

		    public void sendMail(List<String> fileNames) {
		        List<String> sendFile = new ArrayList<String>();
		        // 判断本地服务器是否存在网银明细文件文件
		        for (Iterator<String> it = fileNames.iterator(); it.hasNext();) {
		            String fileName = it.next();
		            if (!(new File(filePath + "xls/" + fileName).exists())) {
		                logger.error("IcbcQryAcctDetailController sendMail " + fileName + " is not exist");
		                it.remove();
		                continue;
		            }
		            sendFile.add(filePath + "xls/" + fileName);
		            logger.error("IcbcQryAcctDetailController sendFile: " + sendFile);
		        }

		        logger.error("IcbcQryAcctDetailController fileNames: " + fileNames + "fileNames.size: " + fileNames.size()
		                + "sendFile.size: " + sendFile.size());

		        if (fileNames.size() > 0) {
		            String subject = "工行银企直联网银明细文件";
		            String messageText = "附件为工行银企直联网银明细文件, 请查收!";
		            String[] cc = acctDetailConfig.get("sendmail.cc").split(","); // 获取多个抄送人
		            List<String> ccList = Arrays.asList(cc);

		            try {
		                SendMail.sendMessage(acctDetailConfig.get("sendmail.host"), acctDetailConfig.get("sendmail.sender"),
		                        acctDetailConfig.get("sendmail.username"), acctDetailConfig.get("sendmail.password"),
		                        acctDetailConfig.get("sendmail.receiver"), subject, messageText, "text/html;charset=gb2312",
		                        ccList, sendFile, fileNames);
		            } catch (MessagingException e) {
		                logger.error("IcbcQryAcctDetailController sendMail error: ", e);
		            }
		        }
		    }
		    
		    /**
		     * 获取网银明细文件最后一行数据
		     * 
		     * @param file
		     * @param charset
		     * @return
		     * @throws IOException
		     */
		    public static String readLastLine(File file, String charset) throws IOException {
		        if (!file.exists() || file.isDirectory() || !file.canRead()) {
		            return null;
		        }
		        RandomAccessFile raf = null;
		        try {
		            raf = new RandomAccessFile(file, "r");
		            long len = raf.length();
		            if (len == 0L) {
		                return "";
		            } else {
		                long pos = len - 1;
		                while (pos > 0) {
		                    pos--;
		                    raf.seek(pos);
		                    if (raf.readByte() == '\n') {
		                        break;
		                    }
		                }
		                if (pos == 0) {
		                    raf.seek(0);
		                }
		                byte[] bytes = new byte[(int) (len - pos)];
		                raf.read(bytes);
		                if (charset == null) {
		                    return new String(bytes);
		                } else {
		                    return new String(bytes, charset);
		                }
		            }
		        } catch (FileNotFoundException e) {
		        } finally {
		            if (raf != null) {
		                try {
		                    raf.close();
		                } catch (Exception e2) {
		                }
		            }
		        }
		        return null;
		    }

		    public String getNextTag(String file) throws IOException  {
		        String nextTag = "";
		        RandomAccessFile rf = null;

		        rf = new RandomAccessFile(file, "r");
		        long len = rf.length();
		        long start = rf.getFilePointer();
		        long nextend = start + len - 1;
		        String line;
		        rf.seek(nextend);
		        int c = -1;
		        while (nextend > start) {
		            c = rf.read();
		            if (c == '\n' || c == '\r') {
		                line = rf.readLine();
		                if (line != null) {
		                    if (line.contains("<NextTag>")) {
		                        nextTag = line.substring(line.indexOf("<NextTag>") + 9, line.indexOf("</NextTag>"));
		                        break;
		                    }

		                }

		                nextend--;
		            }
		            nextend--;
		            rf.seek(nextend);
		            // if (nextend == 0) {// 当文件指针退至文件开始处，输出第一行
		            // System.out.println("333" + rf.readLine());
		            // }
		        }
		        logger.error("IcbcQryAcctDetailController getNextTag: " + nextTag);
		        return nextTag;
		    }
		    
		    /**
		     * 查询工行银企付款订单
		     * 
		     * @param date
		     * @return
		     * @throws ParseException
		     */
		    public int getBcdlOrderCount(String date) throws ParseException {
		        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
		        Date queryDate = formatter.parse(date); // 取时间
		        Calendar calendar = new GregorianCalendar();
		        calendar.setTime(queryDate);
		        calendar.add(calendar.DATE, 1);
		        queryDate = calendar.getTime();
		        String endTime = formatter.format(queryDate);
		        logger.error("IcbcQryAcctDetailController getBcdlOrderCount beginTime: " + date + "endTime: " + endTime);

		        StringBuffer sql = new StringBuffer(250);
		        sql.append("select b.GOPAY_OUT_ORDER_ID,b.GOPAY_ORDER_ID from BIS_BANK_MAIN_ORDER b ");
		        sql.append(" where b.BANK_BIZ_PROD_CODE = :bankBizProdCode ");
		        sql.append(" and b.ORDER_TYPE = :orderType ");
		        sql.append(" and b.GOPAY_BANK_CODE = :bankCode ");
		        sql.append(" and b.SEND_BANK_TM >=to_date(:beginTime ,'yyyymmdd') ");
		        sql.append(" and b.SEND_BANK_TM <to_date(:endTime ,'yyyymmdd') ");

		        Map<String, Object> params = new HashMap<String, Object>();
		        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
		        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
		        params.put("bankCode", BankConstants.BANK_LOGO_ICBC);
		        params.put("beginTime", date);
		        params.put("endTime", endTime);
		        List<Object[]> list = commonQueryDAO.findBySql(sql.toString(), params);

		        List<String> orderIds = new ArrayList<String>();
		        for (Object[] bankOrderId : list) {
		            orderIds.add(StringTools.checkNulltoString(bankOrderId[0]));
		        }
		        logger.error("IcbcQryAcctDetailController getBcdlOrderCount size: " + orderIds.size());
		        int size = orderIds.size() / COUNT_SIZE + ((orderIds.size() % COUNT_SIZE == 0) ? 0 : 1);
		        return size;
		    }
}
